package com.bestv.search.common.dao.hibernate;

import com.bestv.search.common.dao.InfoDao;
import com.bestv.search.common.model.Info;
import com.bestv.search.common.model.TopNCache;
import com.bestv.search.common.util.DateUtil;
import com.bestv.search.common.util.StringConst;
import org.hibernate.*;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import java.math.BigInteger;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

public class InfoDaoHibernate extends GenericDaoHibernate<Info, Long> implements InfoDao {

    public InfoDaoHibernate() {
        super(Info.class);
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> getInfoListByCode(String code) {
        return super.getHibernateTemplate().find("from Info where code = ?", code);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Info> getInfoLikeName(String name, int start, int limit) {
        String sql = "from Info where name like '%" + name + "%' and updateFlag != 2 and status = 1 and p_type!='节目单'";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createQuery(sql);
            query.setFirstResult(start);
            query.setMaxResults(limit);

            return query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return null;
    }

    @Override
    public int getInfoCountLikeName(String name) {
        String sql = "select count(*) from Info where name like '%" + name
                + "%' and updateFlag != 2 and status = 1 and p_type!='节目单'";
        Session session = null;
        try {
            session = getSession();
            Query query = session.createQuery(sql);
            Object obj = query.uniqueResult();
            if (obj != null) {
                return Integer.valueOf(obj.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Info> getInfoByNames(String[] names, String p_type) {
        StringBuilder sb = new StringBuilder();

        String[] dbNames = getDBNames(names);
        for (int i = 0; i < dbNames.length; i++) {
            sb.append("?");
            if (i != dbNames.length - 1) {
                sb.append(",");
            }
        }

        String hql = "from Info where name in ( " + sb.toString() + ") and p_type = '" + p_type
                + "' and updateFlag != 2 and status = 1";
        return getHibernateTemplate().find(hql, dbNames);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Info> getInfoByNames(String[] names) {
        StringBuilder sb = new StringBuilder();

        String[] dbNames = getDBNames(names);
        for (int i = 0; i < dbNames.length; i++) {
            sb.append("?");
            if (i != dbNames.length - 1) {
                sb.append(",");
            }
        }

        String hql = "from Info where name in ( " + sb.toString() + ")" + " and updateFlag != 2 and status = 1";
        return getHibernateTemplate().find(hql, dbNames);
    }

    @Override
    public String getNetInfoIdByMediaCode(String mediaCode) {
        String sql = "select netInfo_id from info left join info_netInfo on info.id = info_netInfo.info_id"
                + " where info.code= ? and info.update_flag<>2";
        Session session = null;
        String id = StringConst.EMPTY;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql);
            query.setString(0, mediaCode);

            List<?> list = query.list();
            if (list != null && list.size() > 0) {
                if (list.get(0) != null) {
                    id = list.get(0).toString();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return id;
    }

    @Override
    public List<Object[]> getComparedInfo(String mediaCode, String p_type) {
        String sql = null;
        if (p_type != null && p_type.equals("电视剧")) {
            sql = "select info.name as localName, info.p_type as localType, info.genre as localGenre,"
                    + " series.region as localArea, series.actors as localActors, "
                    + " series.directors as localDirectors, info.description as localDescription, "
                    + " netInfo.name as netName, netInfo.category as netType,  netInfo.genre as netGenre,"
                    + " netInfo.area as netArea,netInfo.released as netReleased,"
                    + " netInfo.performers as netActors,netInfo.directors as netDirectors,"
                    + " netInfo.description as netDescription,netInfo.original_name,netInfo.alias_name,"
                    + " netInfo.youkuScore, netInfo.doubanScore"
                    + " from info inner join info_netInfo on info.id = info_netInfo.info_id"
                    + " inner join netInfo on netInfo.id=info_netInfo.netInfo_id"
                    + " inner join series on info.id=series.id where info.update_flag<>2 and code=?";
        } else {
            sql = "select info.name as localName, info.p_type as localType, info.genre as localGenre,"
                    + " program.region as localArea, program.issue_year as localReleased,"
                    + " program.actors as localActors, program.directors as localDirectors, info.description as localDescription, "
                    + " netInfo.name as netName, netInfo.category as netType,  netInfo.genre as netGenre,"
                    + " netInfo.area as netArea,netInfo.released as netReleased,"
                    + " netInfo.performers as netActors,netInfo.directors as netDirectors,"
                    + " netInfo.description as netDescription,netInfo.original_name,netInfo.alias_name,"
                    + " netInfo.youkuScore, netInfo.doubanScore"
                    + " from info inner join info_netInfo on info.id = info_netInfo.info_id"
                    + " inner join netInfo on netInfo.id=info_netInfo.netInfo_id"
                    + " inner join program on info.id=program.id where info.update_flag<>2 and code=?";
        }

        Session session = null;
        List<Object[]> rs = new ArrayList<Object[]>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.setString(0, mediaCode);

            List<?> list = query.list();
            if (list != null && list.size() > 0) {
                if (list.get(0) != null) {
                    @SuppressWarnings("rawtypes")
                    Map map = (Map) list.get(0);
                    // each String array has three element, the first is field
                    // name, the second is local value, the third is net value
                    rs.add(new Object[]{"名称", map.get("localName"), map.get("netName")});
                    rs.add(new Object[]{"原有名", null, map.get("original_name")});
                    rs.add(new Object[]{"别名", null, map.get("alias_name")});
                    rs.add(new Object[]{"类型", map.get("localType"), map.get("netType")});
                    rs.add(new Object[]{"Genre", map.get("localGenre"), map.get("netGenre")});
                    rs.add(new Object[]{"地区", map.get("localArea"), map.get("netArea")});
                    rs.add(new Object[]{"导演", map.get("localDirectors"), map.get("netDirectors")});
                    rs.add(new Object[]{"演员", map.get("localActors"), map.get("netActors")});
                    if (p_type != null && p_type.equals("电视剧")) {
                        rs.add(new Object[]{"发布日期", null, map.get("netReleased")});
                    } else {
                        rs.add(new Object[]{"发布日期", map.get("localReleased"), map.get("netReleased")});
                    }
                    rs.add(new Object[]{"豆瓣评分", null, map.get("youkuScore")});
                    rs.add(new Object[]{"优酷评分", null, map.get("doubanScore")});
                    rs.add(new Object[]{"描述", map.get("localDescription"), map.get("netDescription")});
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }

        return rs;
    }

    @Override
    public List<Info> getInfos() {
        return super.getAll();
    }

    @Override
    public int getOnLineInfosCount() {
        String sql = "select count(*) from info where update_flag<2";
        Session session = null;
        try {
            Query query = getSession().createSQLQuery(sql);
            List<?> list = query.list();

            if (list != null && list.size() > 0) {
                return Long.valueOf(list.get(0).toString()).intValue();
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Info> getOnLineInfos(int start, int limit) {
        String hql = "from Info info where info.updateFlag < 2 order by info.updateTimeForIndex asc";
        Session session = null;
        List<Info> infos = null;
        try {
            Query query = getSession().createQuery(hql);
            query.setFirstResult(start);
            query.setMaxResults(limit);
            infos = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return infos;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Info> getInfosByUpdateTimeForIndex(Date updateTimeForIndex) {
        String hql = "from Info info where info.updateFlag < 3 and info.updateTimeForIndex >=:updateTimeForIndex "
                + " order by info.updateTimeForIndex asc";
        Session session = null;
        List<Info> infos = null;
        try {
            Query query = getSession().createQuery(hql);
            query.setTimestamp("updateTimeForIndex", updateTimeForIndex);
            infos = query.list();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return infos;
    }

    @Override
    public Info saveInfo(Info info) {
        return super.save(info);
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> getInfoByCode(String code) {
        return getHibernateTemplate().find("from Info where code = ?", code);
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> getInfoByCode(List<String> codes) {
        if (codes.size() > 0) {
            StringBuilder hql = new StringBuilder("from Info where code = ?");
            for (int i = 1; i < codes.size(); i++) {
                hql.append(" or code =?");
            }
            return super.getHibernateTemplate().find(hql.toString(), codes.toArray());
        }
        return new ArrayList<Info>();
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> searchInfoByNames(String[] names, int firstResult, int maxResult) {
        String hql = "from Info where name like '%" + names[0] + "%'";
        for (int i = 1; i < names.length; ++i) {
            hql += " or name like '%" + names[i] + "%'";
        }
        log.debug(hql);
        Query query = this.getSession().createQuery(hql);
        query.setFirstResult(firstResult);
        query.setMaxResults(maxResult);
        return query.list();
    }

    @Override
    public int getRowCount(String[] names) {
        String hql = "select count(*) from Info where name like '%" + names[0] + "%'";
        for (int i = 1; i < names.length; ++i) {
            hql += " or name like '%" + names[i] + "%'";
        }
        log.debug(hql);
        Query query = this.getSession().createQuery(hql);
        String totalCount = query.uniqueResult().toString();
        log.debug(totalCount);
        return Integer.parseInt(totalCount);
    }

    @Override
    @SuppressWarnings("unchecked")
    public Set<Info> getInfoSetByIds(String ids, Map<String, String> otherConditions) throws ParseException {
        String hql = "from Info info where info.id in " + ids;
        Set<String> keys = otherConditions.keySet();
        Date df = null, dt = null;
        for (String key : keys) {
            if ("subtype".equals(key) && !"".endsWith(otherConditions.get(key))) {
                String subTypes = "(" + otherConditions.get(key).replaceAll("[ ]+", ",");
                hql += " and info.subType.name in " + subTypes;
            } else if ("updateTime".equals(key)) {
                String ut = otherConditions.get(key);
                String[] updateTimes = ut.split("TO");
                if (updateTimes.length == 2) {
                    String from = updateTimes[0].trim();
                    String to = updateTimes[1].trim();
                    if (from.matches("\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}")) {
                        df = DateUtil.convertStringToDate("yyyy-MM-dd HH:mm", from);
                        hql += " and info.updateTime >=:from";
                    }
                    if (to.matches("\\d{4}-\\d{1,2}-\\d{1,2} \\d{1,2}:\\d{1,2}")) {
                        dt = DateUtil.convertStringToDate("yyyy-MM-dd HH:mm", to);
                        hql += " and info.updateTime <=:to";
                    }
                }
            }
        }
        final String hqlStr = hql;
        final Date dateFrom = df, dateTo = dt;
        log.debug(hqlStr);
        return (Set<Info>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query q = session.createQuery(hqlStr);
                if (dateFrom != null)
                    q.setTimestamp("from", dateFrom);
                if (dateTo != null)
                    q.setTimestamp("to", dateTo);
                return new HashSet<Info>(q.list());
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public Set<Info> getInfoSetByIds(Object ids) {
        if (ids instanceof String) {
            String s = (String) ids;
            String hql = "from Info where id in " + s;
            List<Info> list = getHibernateTemplate().find(hql);
            return new HashSet<Info>(list);
        }
        if (!(ids instanceof Collection)) {
            log.error("Params wrong,ids must be String or List<Long>");
            return null;
        }
        Collection<Long> lds = (Collection<Long>) ids;
        StringBuffer inCondition = new StringBuffer("(");
        for (Long id : lds) {
            inCondition.append(id);
            inCondition.append(",");
        }
        inCondition.deleteCharAt(inCondition.lastIndexOf(","));
        inCondition.append(")");
        log.debug(inCondition.toString());
        if ("()".equals(inCondition.toString()))
            return null;
        String hql = "from Info where id in " + inCondition.toString();
        log.debug("hql: " + hql);
        List<Info> list = getHibernateTemplate().find(hql);
        Set<Info> set = new HashSet<Info>(list);
        return set;
    }

    @Override
    @SuppressWarnings("unchecked")
    public Set<Info> getInfoSetByCodes(Object codes) {
        if (codes instanceof String) {
            String s = (String) codes;
            String hql = "from Info where code in " + s;
            List<Info> list = getHibernateTemplate().find(hql);
            return new HashSet<Info>(list);
        }
        if (!(codes instanceof Collection)) {
            log.error("Params wrong,ids must be String or List<Long>");
            return null;
        }
        Collection<String> cos = (Collection<String>) codes;
        StringBuffer inCondition = new StringBuffer("(");
        for (String id : cos) {
            inCondition.append("'").append(id).append("'");
            inCondition.append(",");
        }
        if (inCondition.lastIndexOf(",") >= 0)
            inCondition.deleteCharAt(inCondition.lastIndexOf(","));
        inCondition.append(")");
        log.debug(inCondition.toString());
        if ("()".equals(inCondition.toString()))
            return null;
        String hql = "from Info where code in " + inCondition.toString();
        log.debug("hql: " + hql);
        List<Info> list = getHibernateTemplate().find(hql);
        HashSet<Info> set = new HashSet<Info>(list);
        return set;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<String> findPTypes() {
        return (List<String>) getHibernateTemplate().execute(new HibernateCallback() {
            public List<String> doInHibernate(Session session) throws HibernateException, SQLException {
                String nativeSql = "select distinct p_type from info where p_type != ''";
                Query query = session.createSQLQuery(nativeSql);
                return query.list();
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Integer[]> findProAndKeyNumInPTypes() {
        return (Map<String, Integer[]>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                // String sql =
                // "select kk.type as type,tt.tnum as tnum,kk.keynum as keynum from (select info.p_type as type,count(distinct keyword) as keynum  from info left join keyword as k on k.info_id = info.id group by info.p_type) as kk inner join (select p_type as type, count(p_type) as  tnum  from info group by p_type) as tt on kk.type=tt.type";
                String sql = "select kk.p_type as type,kk.num as keynum,tt.num as tnum from "
                        + "(select p_type.id as pid,p_type.p_type,count(distinct info_key.key_id) as num from "
                        + "(select id,p_type_id from info where info.update_flag != 2) as temp_info "
                        + " inner join p_type on temp_info.p_type_id = p_type.id "
                        + " left join info_key on temp_info.id = info_key.info_id group by pid) as kk "
                        + " inner join " + "(select p_type.id as pid,count(info.id) as num from info "
                        + " inner join p_type on info.p_type_id = p_type.id group by pid) as tt "
                        + " on kk.pid = tt.pid order by tnum desc";
                SQLQuery sqlQuery = session.createSQLQuery(sql).addScalar("type", Hibernate.STRING)
                        .addScalar("tnum", Hibernate.INTEGER).addScalar("keynum", Hibernate.INTEGER);
                List<Object[]> list = sqlQuery.list();
                Map<String, Integer[]> result = new HashMap<String, Integer[]>();
                Iterator<Object[]> it = list.iterator();
                while (it.hasNext()) {
                    Object[] rows = it.next();
                    result.put(
                            rows[0] == null ? "" : String.valueOf(rows[0]),
                            new Integer[]{new Integer(String.valueOf(rows[1])), new Integer(String.valueOf(rows[2]))});
                }
                return result;
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Integer[]> findProAndTagNumInPTypes() {
        return (Map<String, Integer[]>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                // String sql =
                // "select tt.ptype as type,ff.p_num as pnum,tt.tag_num as tnum from (select info.p_type as ptype,count(distinct tag) as tag_num from info left join tag as t on t.info_id = info.id group by ptype) as tt inner join (select p_type as ptype, count(p_type) as  p_num  from info group by p_type) as ff on tt.ptype=ff.ptype";
                String sql = "select kk.p_type as type,kk.num as tnum,tt.num as pnum from "
                        + "(select p_type.id as pid,p_type.p_type,count(distinct info_tag.tag_id) as num from "
                        + "(select id,p_type_id from info where info.update_flag != 2) as temp_info  "
                        + " inner join p_type on temp_info.p_type_id = p_type.id "
                        + "left join info_tag on temp_info.id = info_tag.info_id group by pid) as kk " + "inner join "
                        + "(select p_type.id as pid,count(info.id) as num from info "
                        + " inner join p_type on info.p_type_id = p_type.id group by pid) as tt "
                        + " on kk.pid = tt.pid order by tnum desc";
                SQLQuery sqlQuery = session.createSQLQuery(sql).addScalar("type", Hibernate.STRING)
                        .addScalar("pnum", Hibernate.INTEGER).addScalar("tnum", Hibernate.INTEGER);
                List<Object[]> list = sqlQuery.list();
                Map<String, Integer[]> result = new HashMap<String, Integer[]>();
                Iterator<Object[]> it = list.iterator();
                while (it.hasNext()) {
                    Object[] rows = it.next();
                    result.put(
                            rows[0] == null ? "" : String.valueOf(rows[0]),
                            new Integer[]{new Integer(String.valueOf(rows[1])), new Integer(String.valueOf(rows[2]))});
                }
                return result;
            }
        });
    }

    @Override
    public String[][] findEachKeyAndNumInPType(final String ptype, final int start, final int limit) {
        return (String[][]) getHibernateTemplate().execute(new HibernateCallback() {
            @SuppressWarnings("unchecked")
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String sql;
                SQLQuery sqlQuery;
                if ("all".equals(ptype)) {
                    sql = "select keyword,count(key_id) as num from (select id from info where update_flag !=2) as temp_info inner join info_key on temp_info.id = info_key.info_id inner join keyword on info_key.key_id =keyword.id group by key_id order by num desc";
                    sqlQuery = session.createSQLQuery(sql).addScalar("keyword", Hibernate.STRING)
                            .addScalar("num", Hibernate.INTEGER);
                } else {
                    sql = "select keyword.keyword,count(keyword.id) as num from "
                            + "(select id,p_type_id from info where update_flag != 2) as temp_info "
                            + "inner join (select id from p_type where p_type=:ptype) as temp_type on temp_type.id = temp_info.p_type_id "
                            + "inner join info_key on temp_info.id = info_key.info_id "
                            + "inner join keyword on info_key.key_id = keyword.id group by keyword.id order by num desc";
                    sqlQuery = session.createSQLQuery(sql).addScalar("keyword", Hibernate.STRING)
                            .addScalar("num", Hibernate.INTEGER);
                    sqlQuery.setString("ptype", ptype);
                }
                sqlQuery.setFirstResult(start);
                sqlQuery.setMaxResults(limit);
                String[][] st = new String[2][];
                List<Object[]> list = sqlQuery.list();
                st[0] = new String[list.size()];
                st[1] = new String[list.size()];
                for (int i = 0; i < list.size(); ++i) {
                    Object[] rows = list.get(i);
                    st[0][i] = String.valueOf(rows[0]);
                    st[1][i] = String.valueOf(rows[1]);
                }
                return st;
            }
        });
    }

    @Override
    public int getDistinctCountOfKeysInPtype(final String ptype) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String sql;
                Query sqlQuery;
                if ("all".equals(ptype)) {
                    sql = "select count(distinct info_key.key_id) as num from (select id from info where update_flag != 2) as temp_info inner join info_key on temp_info.id = info_key.info_id";
                    sqlQuery = session.createSQLQuery(sql);
                } else {
                    sql = "select count(distinct info_key.key_id) from (select id from info where update_flag != 2 and p_type_id = (select id from p_type where p_type =:ptype)) as temp_info inner join "
                            + "info_key on temp_info.id = info_key.info_id";
                    sqlQuery = session.createSQLQuery(sql);
                    sqlQuery.setString("ptype", ptype);
                }
                BigInteger count = (BigInteger) sqlQuery.uniqueResult();
                return count.intValue();
            }
        });
    }

    @Override
    public int getDistinctCountOfTagsInPtype(final String ptype) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String sql;
                Query sqlQuery;
                if ("all".equals(ptype)) {
                    // sql =
                    // "select count(distinct tag) from info inner join tag on info.id=tag.info_id";
                    sql = "select count(distinct info_tag.tag_id) as num from (select id from info where update_flag != 2) as temp_info inner join info_tag on temp_info.id = info_tag.info_id";
                    sqlQuery = session.createSQLQuery(sql);
                } else {
                    // sql =
                    // "select count(distinct tag) from info inner join tag on info.id=tag.info_id and info.p_type=:ptype";
                    sql = "select count(distinct info_tag.tag_id) from (select id from info where update_flag != 2 and p_type_id = (select id from p_type where p_type =:ptype)) as temp_info inner join "
                            + "info_tag on temp_info.id = info_tag.info_id";
                    sqlQuery = session.createSQLQuery(sql);
                    sqlQuery.setString("ptype", ptype);
                }
                BigInteger count = (BigInteger) sqlQuery.uniqueResult();
                return count.intValue();
            }
        });
    }

    @Override
    public String[][] findEachTagAndNumInPtype(final String ptype, final int start, final int limit) {
        return (String[][]) getHibernateTemplate().execute(new HibernateCallback() {
            @SuppressWarnings("unchecked")
            public Object doInHibernate(Session session) {
                String sql;
                SQLQuery sqlQuery;
                if ("all".equals(ptype)) {
                    // sql =
                    // "select t.tag as tag,count(tag) as num from tag as t inner join info on t.info_id=info.id group by tag order by num desc";
                    sql = "select tag,count(tag_id) as num from (select id from info where update_flag !=2) as temp_info inner join info_tag on temp_info.id = info_tag.info_id inner join tag on info_tag.tag_id =tag.id group by tag_id order by num desc";
                    sqlQuery = session.createSQLQuery(sql).addScalar("tag", Hibernate.STRING)
                            .addScalar("num", Hibernate.INTEGER);
                } else {
                    // sql =
                    // "select t.tag as tag,count(tag) as num from tag as t inner join info on t.info_id=info.id and info.p_type=:ptype group by tag order by num desc";
                    sql = "select tag.tag,count(tag.id) as num from "
                            + "(select id,p_type_id from info where update_flag != 2) as temp_info "
                            + "inner join (select id from p_type where p_type=:ptype) as temp_type on temp_type.id = temp_info.p_type_id "
                            + "inner join info_tag on temp_info.id = info_tag.info_id "
                            + "inner join tag on info_tag.tag_id = tag.id group by tag.id order by num desc";
                    sqlQuery = session.createSQLQuery(sql).addScalar("tag", Hibernate.STRING)
                            .addScalar("num", Hibernate.INTEGER);
                    sqlQuery.setString("ptype", ptype);
                }
                sqlQuery.setFirstResult(start);
                sqlQuery.setMaxResults(limit);
                String[][] st = new String[2][];
                List<Object[]> list = sqlQuery.list();
                st[0] = new String[list.size()];
                st[1] = new String[list.size()];
                for (int i = 0; i < list.size(); ++i) {
                    Object[] rows = list.get(i);
                    st[0][i] = String.valueOf(rows[0]);
                    st[1][i] = String.valueOf(rows[1]);
                }
                return st;
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Integer> findCrossKeyword() {
        return (List<Integer>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List<Integer> list = new ArrayList<Integer>();
                String newFin = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='新闻')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='财经')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";
                String newEnt = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='新闻')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='娱乐')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";

                String finEnt = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='财经')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='娱乐')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";

                String newSer = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='新闻')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='电视剧')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";

                String finSer = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='财经')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='电视剧')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";
                String entSer = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type='娱乐')) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type='电视剧')) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";
                String sql = new StringBuffer(newFin).append(" union all ").append(newEnt).append(" union all ")
                        .append(finEnt).append(" union all ").append(newSer).append(" union all ").append(finSer)
                        .append(" union all ").append(entSer).toString();

                SQLQuery sqlQuery = session.createSQLQuery(sql);
                List<BigInteger> tempList = sqlQuery.list();
                for (BigInteger bi : tempList)
                    list.add(bi.intValue());
                return list;
            }
        });
    }

    @Override
    public int findCrossKeyCount(final String ptype1, final String ptype2) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String sql = "select count(distinct kid) from "
                        + " ( select kid from "
                        + "( select distinct ik.key_id as kid from info_key ik inner join "
                        + "(select id from info info where  info.update_flag != 2 and info.p_type_id=(select id from p_type tp where tp.p_type=:ptype1)) "
                        + " as tp_info on ik.info_id = tp_info.id "
                        + "union all "
                        + "select distinct ik2.key_id as kid from info_key ik2 inner join "
                        + "(select id from info info2 where  info2.update_flag != 2 and  info2.p_type_id=(select id from p_type tp2 where tp2.p_type=:ptype2)) "
                        + "as tp_info2 on ik2.info_id = tp_info2.id "
                        + ") as temp_table group by kid having count(kid) >= 2 " + ") as tb";
                SQLQuery sq = session.createSQLQuery(sql);
                sq.setString("ptype1", ptype1);
                sq.setString("ptype2", ptype2);
                BigInteger bi = (BigInteger) sq.uniqueResult();
                return bi.intValue();
            }

        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<String> findCrossKeywordDetail(final String ptype1, final String ptype2, final int start,
                                               final int limit) {
        return (List<String>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                List<String> list = new ArrayList<String>();
                StringBuffer sb = new StringBuffer("select keyword from keyword inner join ( ");
                sb.append("select kid as id from ");
                sb.append("( ")
                        .append("select distinct ik.key_id as kid from info_key ik inner join ")
                        .append("(select id from info where update_flag != 2 and p_type_id=(select id from p_type tp where tp.p_type=:ptype1)) ")
                        .append("as ti on ik.info_id = ti.id");
                sb.append(" union all ");
                sb.append("select distinct ik2.key_id as kid from info_key ik2 inner join ")
                        .append("(select id from info where update_flag != 2 and p_type_id=(select id from p_type tp2 where tp2.p_type=:ptype2)) ")
                        .append("as ti2 on ik2.info_id = ti2.id ");
                sb.append(") ");
                sb.append("as temp_table group by kid having count(kid) >= 2 order by count(kid) desc,kid");
                sb.append(") as tb on keyword.id = tb.id ");
                if (start >= 0 && limit > 0)
                    sb.append("limit ").append(start).append(",").append(limit);
                String sql = sb.toString();
                SQLQuery sqlQuery = session.createSQLQuery(sql);
                sqlQuery.setString("ptype1", ptype1);
                sqlQuery.setString("ptype2", ptype2);
                sqlQuery.addScalar("keyword", Hibernate.STRING);
                Iterator<String> it = sqlQuery.list().iterator();
                while (it.hasNext()) {
                    String rows = it.next();
                    list.add(String.valueOf(rows));
                }
                return list;
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> getInfosByType(final int stat, final int lmt, final String type) {
        return (List<Info>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String hql = "from " + type;
                Query query = session.createQuery(hql);
                if (stat > 0 && lmt > 0) {
                    query.setFirstResult(stat);
                    query.setMaxResults(lmt);
                }
                return query.list();
            }
        });
    }

    @Override
    @SuppressWarnings({"rawtypes"})
    public int getCountByType(final String infoType) {
        List list = getHibernateTemplate().find("from " + infoType);
        return list == null ? 0 : list.size();
    }

    @Override
    public void delete(final Long id) {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                String sql = "delete from info_manual_bind where info_id = " + id;
                SQLQuery sq = session.createSQLQuery(sql);
                sq.executeUpdate();
                Info info = (Info) session.get(Info.class, id);
                if (info != null)
                    session.delete(info);
                return null;
            }
        });
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<Info> getOverdueSchedule(int validPeiod) {
        Date now = new Date();
        Date overdueTime = new Date(now.getTime() - validPeiod * 60 * 60 * 1000);
        return getHibernateTemplate().find("from Schedule s where s.startPlayTime < ?", overdueTime);
    }

    /**
     * Save Info List for Data save
     */
    @Override
    public void saveInfoList(List<Info> list) {
        getHibernateTemplate().saveOrUpdateAll(list);
    }

    @Override
    public void saveUpdateInfoList(final List<Info> list) {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                for (Info info : list) {
                    session.saveOrUpdate(info);
                    session.flush();
                    session.clear();
                }
                return null;
            }
        });
    }

    @Override
    public void saveUpdateInfo(final Info info) {
        getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                session.saveOrUpdate(info);
                session.flush();
                session.clear();
                return null;
            }
        });
    }

    private String[] getDBNames(String[] names) {
        List<String> dbNames = new ArrayList<String>();
        for (String name : names) {
            dbNames.add(name);
            dbNames.add(name + "[粤]");
            dbNames.add(name + "-原声版");
            dbNames.add(name + "-中文版");
            dbNames.add(name + "-国语版");
            dbNames.add(name + "[英]");
            dbNames.add("HD-" + name);
            dbNames.add("HD-" + name + "[粤]");
            dbNames.add("HD-" + name + "-原声版");
            dbNames.add("HD-" + name + "-中文版");
            dbNames.add("HD-" + name + "-国语版");
            dbNames.add("HD-" + name + "[英]");
        }
        return dbNames.toArray(new String[dbNames.size()]);
    }

    @Override
    public List<TopNCache> getInfosTopNCache(String category, String topNType) {
        // set default top count to -1 that means use default count lie in pro
        // getTopNInfos
        int defaultTopCount = -1;
        if (StringConst.DAILY.equals(topNType) || StringConst.WEEKLY.equals(topNType)
                || StringConst.MONTHLY.equals(topNType) || StringConst.QUARTER.equals(topNType)) {
            return getInfosTopNCache(category, defaultTopCount, topNType);
        } else if (StringConst.TOTAL.equals(topNType)) {
            return getInfosTotalTopNCache(category, 50);
        } else {
            log.error("Unknow TopNCache type " + topNType + ", just support daily/weekly/monthly/quarter/total.");
            return null;
        }
    }

    @SuppressWarnings("unchecked")
    private List<TopNCache> getInfosTopNCache(String category, int top, String topNType) {
        String subType = StringConst.INFO;
        String sql = "call getTopNInfos(?, ?, ?)";

        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("rankNo", Hibernate.INTEGER)
                    .addScalar("name", Hibernate.STRING).addScalar("code", Hibernate.STRING)
                    .addScalar("category", Hibernate.STRING).addScalar("infoFlag", Hibernate.INTEGER)
                    .addScalar("thisCount", Hibernate.INTEGER).addScalar("lastRankNo", Hibernate.INTEGER)
                    .addScalar("lastCount", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(TopNCache.class));
            query.setString(0, topNType);
            // Set topn to -1, that means use default top value of procedure
            query.setInteger(1, top);
            query.setString(2, category);

            List<TopNCache> list = query.list();
            if (list != null && list.size() > 0) {
                for (TopNCache topnCache : list) {
                    topnCache.setType(topNType);
                    topnCache.setSubType(subType);
                    topnCache.setCustomCategory(category);
                    topnCache.setId(Math.abs(topNType.hashCode()) + Math.abs(subType.hashCode())
                            + Math.abs(category.hashCode()) + String.valueOf(topnCache.getRankNo()));
                    topnCache.setIncreaseNo(topnCache.getLastRankNo() - topnCache.getRankNo());
                    topnCache.setUpdateTime(new Date());
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return null;
    }

    @SuppressWarnings("unchecked")
    private List<TopNCache> getInfosTotalTopNCache(String category, int top) {
        String subType = StringConst.INFO;
        String sql = "select t.name, t.code, infoFlag, sum(t.hitCount) as thisCount, t.p_type as category"
                + " from (select info_header.name,info_header.code, 2 as infoFlag, sum(info.hit_count) as hitCount, info_header.p_type from  info_header"
                + " inner join infoHeader_item on infoHeader_item.info_header_id = info_header.id"
                + " inner join info on info.id = infoHeader_item.info_id where update_flag<>2"
                + " group by info_header.name, info_header.code, infoFlag, info.p_type union all"
                + " select info.name, info.code, 0 as infoFlag, info.hit_count as hitCount, info.p_type"
                + " from info left join infoHeader_item on infoHeader_item.info_id = info.id"
                + " where update_flag<>2 and infoHeader_item.info_id is null"
                + " group by name, code, infoFlag, p_type) as t";
        if (category != null && !category.equals(StringConst.TOTAL_CHINESE)) {
            sql += " where t.p_type=?";
        }
        sql += " group by t.name, t.code, infoFlag, t.p_type order by sum(t.hitCount) desc";

        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).addScalar("name", Hibernate.STRING)
                    .addScalar("code", Hibernate.STRING).addScalar("category", Hibernate.STRING)
                    .addScalar("infoFlag", Hibernate.INTEGER).addScalar("thisCount", Hibernate.INTEGER)
                    .setResultTransformer(Transformers.aliasToBean(TopNCache.class));
            if (category != null && !category.equals(StringConst.TOTAL_CHINESE)) {
                query.setString(0, category);
            }
            query.setMaxResults(top);

            List<TopNCache> list = query.list();
            if (list != null && list.size() > 0) {
                int i = 0;
                for (TopNCache topnCache : list) {
                    topnCache.setRankNo(++i);
                    topnCache.setType(StringConst.TOTAL);
                    topnCache.setSubType(subType);
                    topnCache.setCustomCategory(category);
                    topnCache.setId(Math.abs(StringConst.TOTAL.hashCode()) + Math.abs(subType.hashCode())
                            + Math.abs(category == null ? StringConst.TOTAL_CHINESE.hashCode() : category.hashCode())
                            + String.valueOf(topnCache.getRankNo()));
                    topnCache.setUpdateTime(new Date());
                }
            }

            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return null;
    }

    @Override
    public List<String> getInfoCodesByNameGenreTag(String word) {
        String sql = "select distinct info.code from info left join info_tag on info_tag.info_id = info.id "
                + " left join tag on info_tag.tag_id = tag.id where (name like '%" + word + "%' or genre like '%"
                + word + "%' or tag.tag like '%" + word + "%') and update_flag!=2 and status=1";

        List<String> results = new ArrayList<String>();
        Session session = null;
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

            @SuppressWarnings("unchecked")
            List<Map<String, String>> mapList = query.list();
            if (mapList != null && mapList.size() > 0) {
                for (int i = 0; i < mapList.size(); i++) {
                    Map<String, String> map = mapList.get(i);
                    results.add(map.get("code"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return results;
    }

    @Override
    public Map<String, Integer> getMaxHitCountByTags(String p_type) {
        String sql = "select tag.tag, max(hit_count) as maxHitCount from info left join info_tag on info.id=info_tag.info_id"
                + " inner join tag on tag.id = info_tag.tag_id where update_flag<>2 and p_type=? group by tag.tag"
                + " order by max(hit_count) desc";

        Session session = null;
        Map<String, Integer> results = new HashMap<String, Integer>();
        try {
            session = getSession();
            Query query = session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
            query.setString(0, p_type);

            @SuppressWarnings("unchecked")
            List<Map<String, Object>> mapList = query.list();
            if (mapList != null && mapList.size() > 0) {
                for (int i = 0; i < mapList.size(); i++) {
                    Map<String, Object> map = mapList.get(i);
                    results.put(map.get("tag").toString(), Integer.valueOf(map.get("maxHitCount").toString()));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (session != null)
                releaseSession(session);
        }
        return results;
    }
}
